package com.xdwanj.dao.impl;

import com.xdwanj.dao.BaseDao;
import com.xdwanj.dao.OrderDao;
import com.xdwanj.pojo.Order;
import com.xdwanj.util.PageBean;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class OrderDaoImpl implements OrderDao {
    private BaseDao baseDao = new BaseDao();
    JdbcTemplate jdbcTemplate = baseDao.getJdbcTemplate();

    @Override
    public Order findOrderById(int oid) throws Exception {
        String querySql = "select *, o.id, o.c_id commodity_id, o.u_id user_id, c.name com_name\n" +
                "from orders o,\n" +
                "     user_orders uo,\n" +
                "     user u,\n" +
                "     commodity c\n" +
                "where o.id = uo.o_id\n" +
                "  and u.id = uo.u_id\n" +
                "  and o.c_id = c.id\n" +
                "  and o.id = ?";

        return jdbcTemplate.queryForObject(querySql, new BeanPropertyRowMapper<Order>(Order.class), oid);
    }

    @Override
    public int ConversionOrderByCommodity(int UserId, int CommodityId, int com_num) throws Exception {
        String sql = "insert into orders (u_id,c_id,orders_date,state,com_num) values (?,?,?,?,?)";
        return jdbcTemplate.update(sql, UserId, CommodityId, new Date(), 1, com_num);
    }

    @Override
    public int deleteOrder(int oid) throws Exception {
        int deleteRow = 0;
        String deleteOrderSql = "delete from orders where id = ?";
        String deleteOrderAndUserSql = "delete from user_orders where o_id = ?";
        deleteRow += jdbcTemplate.update(deleteOrderSql, oid);
        deleteRow += jdbcTemplate.update(deleteOrderAndUserSql, oid);
        return deleteRow;
    }

    @Override
    public PageBean<Order> findOrderListByUid(int uid, int pageSize, int currentPageNo) throws Exception {
        String sql = " from orders o,\n" +
                "     commodity co\n" +
                "where o.c_id = co.id\n" +
                "  and o.id in (\n" +
                "    select distinct(o.id) oid\n" +
                "    from orders o,\n" +
                "         commodity co,\n" +
                "         user_orders uo\n" +
                "    where o.c_id = co.id\n" +
                "      and o.u_id = uo.u_id\n" +
                "      and o.u_id = ?) ";
        List<Object> params = new ArrayList<>();
        params.add(uid);

        return baseDao.<Order>getCustomerPageDto(new StringBuffer("select * ").append(sql).toString(),
                params.toArray(),
                new BeanPropertyRowMapper<Order>(Order.class),
                " select count(*) " + sql,
                params.toArray(),
                currentPageNo,
                pageSize);
    }

    @Override
    public int updateOrderStatus(int id, int state) throws Exception {
        String sql = "update orders set state = ? where id = ?";
        return jdbcTemplate.update(sql, state, id);
    }

    @Override
    public int insertOrder(final Order order, final int uid, final int coid) throws Exception {
        int orderId = 0;
        int updateRow = 0;
        String insertUserAndOrderSql = "insert into user_orders(u_id, o_id) values (?, ?)";
        final String insertOrderSql = "insert into orders(c_id, u_id, orders_date, state, com_num) values (?, ?, ?, ?, ?)";
        order.setUserId(uid);
        order.setCommodityId(coid);

        KeyHolder keyHolder = new GeneratedKeyHolder();
        updateRow += jdbcTemplate.update(con -> {
            PreparedStatement ps = con.prepareStatement(insertOrderSql, PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setInt(1, coid);
            ps.setInt(2, uid);
            ps.setDate(3, new java.sql.Date(order.getOrdersDate().getTime()));
            ps.setInt(4, order.getState());
            ps.setInt(5, order.getComNum());
            return ps;
        }, keyHolder);
        orderId = keyHolder.getKey().intValue();

        jdbcTemplate.update(insertUserAndOrderSql, uid, orderId);

        return updateRow;
    }

    @Override
    public int updateOrder(Order order, int uid, int coid) throws Exception {
        int updateRow = 0;
        String insertOrderSql = "update orders o\n" +
                "set o.u_id        = ?,\n" +
                "    o.c_id        = ?,\n" +
                "    o.com_num     = ?,\n" +
                "    o.orders_date = ?,\n" +
                "    o.state       = ?\n" +
                "where o.id = ?";

        String updateUserAndOrderSql = "update user_orders\n" +
                "set u_id = ?\n" +
                "where o_id = ?";

        updateRow += jdbcTemplate.update(insertOrderSql,
                uid,
                coid,
                order.getComNum(),
                order.getOrdersDate(),
                order.getState(),
                order.getId());

        updateRow += jdbcTemplate.update(updateUserAndOrderSql, uid, order.getId());

        return updateRow;
    }

    @Override
    public PageBean<Order> findOrderList(int currentPageNo, int pageSize) throws Exception {
        String querySql = " from orders o,\n" +
                "     user_orders uo,\n" +
                "     user u,\n" +
                "     commodity c\n" +
                "where o.id = uo.o_id\n" +
                "  and u.id = uo.u_id\n" +
                "  and o.c_id = c.id";

        return baseDao.<Order>getCustomerPageDto(" select *, o.id id, u.id user_id, c.id commodity_id, c.name com_name " + querySql,
                null,
                new BeanPropertyRowMapper<Order>(Order.class),
                "select count(*) " + querySql,
                null,
                currentPageNo,
                pageSize);
    }
}
